import os
import sqlite3
import hashlib
import logging
from datetime import datetime


DB_NAME = "information_manager.db"



def create_database():
    logging.info("开始创建数据库表结构")
    try:
        conn = connect_db()
        cursor = conn.cursor()
        logging.info("数据库连接成功")
        
        # 创建用户表
        cursor.execute('''CREATE TABLE IF NOT EXISTS users (
                            id INTEGER PRIMARY KEY AUTOINCREMENT,
                            username TEXT UNIQUE NOT NULL,
                            password TEXT NOT NULL,
                            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                        )''')
        logging.info("用户表创建完成")
        logging.info("用户表创建SQL执行完成")
        
        # 创建文件夹表
        cursor.execute('''CREATE TABLE IF NOT EXISTS folders (
                            id INTEGER PRIMARY KEY AUTOINCREMENT,
                            name TEXT NOT NULL,
                            user_id INTEGER NOT NULL,
                            parent_id INTEGER,
                            display_order INTEGER DEFAULT 0,
                            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                            FOREIGN KEY (user_id) REFERENCES users (id),
                            FOREIGN KEY (parent_id) REFERENCES folders (id)
                        )''')
        logging.info("文件夹表创建完成")
        logging.info("文件夹表创建SQL执行完成")
        
        # 添加文件表（之前缺失的部分）
        cursor.execute('''CREATE TABLE IF NOT EXISTS files (
                            id INTEGER PRIMARY KEY AUTOINCREMENT,
                            name TEXT NOT NULL,
                            content TEXT,
                            folder_id INTEGER NOT NULL,
                            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                            FOREIGN KEY (folder_id) REFERENCES folders (id)
                        )''')
        logging.info("文件表创建完成")
        logging.info("文件表创建SQL执行完成")
        
        conn.commit()
        logging.info("数据库事务提交完成")
        conn.close()
        logging.info("数据库连接关闭完成")
        logging.info("数据库表结构创建完成")
    except Exception as e:
        logging.error(f"创建数据库表结构时出错: {str(e)}")
        raise


def is_root_folder(folder_id):
    """判断文件夹是否为根文件夹"""
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute("SELECT name, parent_id FROM folders WHERE id=?", (folder_id,))
    folder = cursor.fetchone()
    conn.close()
    return folder is not None and folder[0] == "我的文档" and folder[1] is None


def database_exists():
    """检查数据库文件是否存在且包含所有表"""
    if not os.path.exists(DB_NAME):
        return False


    try:
        conn = connect_db()
        c = conn.cursor()
        c.execute("SELECT name FROM sqlite_master WHERE type='table'")
        tables = {table[0] for table in c.fetchall()}
        required_tables = {'users', 'folders', 'files'}
        return required_tables.issubset(tables)
    finally:
        conn.close()


def update_database_schema():
    """更新数据库模式以添加新列或表"""
    conn = None
    try:
        conn = connect_db()
        c = conn.cursor()
        
        # 检查folders表是否有display_order列
        c.execute("PRAGMA table_info(folders)")
        columns = [column[1] for column in c.fetchall()]
        if 'display_order' not in columns:
            c.execute("ALTER TABLE folders ADD COLUMN display_order INTEGER DEFAULT 0")
            # 初始化现有记录的display_order值为其在父文件夹中的位置
            c.execute("UPDATE folders SET display_order = (SELECT COUNT(*) FROM folders f2 WHERE f2.parent_id = folders.parent_id AND f2.id < folders.id)")
            conn.commit()
            print("数据库模式已更新: 添加display_order列")
        
        # 新增：检查folders表是否有updated_at列
        if 'updated_at' not in columns:
            c.execute("ALTER TABLE folders ADD COLUMN updated_at TEXT")
            # 初始化现有记录的updated_at值为当前时间
            c.execute("UPDATE folders SET updated_at = datetime('now', 'localtime')")
            conn.commit()
            print("数据库模式已更新: 添加updated_at列")
    except Exception as e:
        print(f"更新数据库模式失败: {str(e)}")
    finally:
        if conn:
            conn.close()


def create_user(username, password):
    conn = connect_db()
    cursor = conn.cursor()
    try:
        # 哈希密码
        hashed_password = hashlib.sha256(password.encode()).hexdigest()
        cursor.execute("INSERT INTO users (username, password) VALUES (?, ?)",
                      (username, hashed_password))
        conn.commit()
        
        # 获取新创建用户的ID
        user_id = cursor.lastrowid
        
        # 自动创建根文件夹"我的文档"
        cursor.execute(
            "INSERT INTO folders (name, user_id, parent_id, display_order, created_at) VALUES (?, ?, ?, ?, ?)",
            ("我的文档", user_id, None, 0, datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
        )
        conn.commit()
        return True
    except sqlite3.IntegrityError:
        return False
    finally:
        conn.close()


def search_files(user_id, keyword):
    """搜索文件标题或内容中包含关键字的文件"""
    conn = connect_db()
    cursor = conn.cursor()
    
    # 使用参数化查询防止SQL注入，同时搜索标题和内容
    query = """
    SELECT f.id, f.name, f.content, f.folder_id 
    FROM files f
    JOIN folders fo ON f.folder_id = fo.id
    WHERE fo.user_id = ? AND (f.name LIKE ? OR f.content LIKE ?)
    ORDER BY f.updated_at DESC
    """
    
    # 使用%作为通配符匹配任意字符
    like_pattern = f"%{keyword}%"
    cursor.execute(query, (user_id, like_pattern, like_pattern))
    results = cursor.fetchall()
    conn.close()
    
    return results


def ensure_user_root_folder(user_id):
    """确保用户有根文件夹，不存在则创建"""
    conn = connect_db()
    cursor = conn.cursor()
    
    # 检查根文件夹是否存在
    cursor.execute("SELECT id FROM folders WHERE user_id=? AND name='我的文档' AND parent_id IS NULL", (user_id,))
    result = cursor.fetchone()
    
    if not result:
        # 创建根文件夹
        cursor.execute(
            "INSERT INTO folders (name, user_id, parent_id, display_order, created_at) VALUES (?, ?, ?, ?, ?)",
            ("我的文档", user_id, None, 0, datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
        )
        conn.commit()
        root_id = cursor.lastrowid
    else:
        root_id = result[0]
    
    conn.close()
    return root_id



def verify_user(username, password):
    """验证用户登录"""
    try:
        conn = connect_db()
        c = conn.cursor()
        hashed_password = hashlib.sha256(password.encode()).hexdigest()
        c.execute("SELECT id FROM users WHERE username=? AND password=?",
                  (username, hashed_password))
        result = c.fetchone()
        return result[0] if result else None
    finally:
        conn.close()



def connect_db():
    """创建数据库连接并启用外键约束"""
    conn = sqlite3.connect(DB_NAME)
    conn.execute("PRAGMA foreign_keys = ON;")
    return conn



if __name__ == "__main__":
    create_database()